# -*- coding: utf-8 -*-
# @Time    : 2021/7/22 17:54
# @Author  : 十三
# @Email   : 2429120006@qq.com
# @File    : base_sql.py
# @Software: PyCharm
import configparser
import random

import pymysql
from DBUtils.PooledDB import PooledDB
from MD5._MD5 import SM4Utils

import re
import json


class DataBase:
    def __init__(self):
        self.SM4 = SM4Utils()
        self.init()

    def insert(self, sql):
        pass

    #
    def getMenuList(self, authority):
        # 获得数据库连接池
        conn = self.DBconnect_Pool.connection()
        conn.ping(reconnect=True)
        cur = conn.cursor()
        col = """SHOW COLUMNS FROM menulist"""
        cur.execute(col)
        conn.commit()
        index = cur.fetchall()  # 返回字段
        _index = [x[0] for x in index]
        try:
            SqlMenuList = """
                    SELECT * from %s 
                    """ % (self.cf.get('DBconnect', 'menu_table'))
            cur.execute(SqlMenuList)
            conn.commit()
            result = cur.fetchall()
            Menu = []
            resultList = []
            if result:  # 返回不为空
                for item in result:
                    resultDict = {}
                    for i in range(len(_index)):
                        resultDict[_index[i]] = item[i]
                    authorityNum = resultDict['authority'].replace("'", '')
                    pattern = re.compile('\d+')
                    itemResult = pattern.findall(authorityNum)
                    if str(authority) in itemResult:
                        resultList.append(resultDict)
                        del resultDict['authority']  # 删除权限的键值
                for item in resultList:
                    lst = []
                    ite = item['children'].replace("'", '')
                    pattern = re.compile('\d+')
                    itemResult = pattern.findall(ite)
                    for it in itemResult:
                        for items in resultList:
                            if items['menuid'] == int(it):
                                lst.append(items)
                                item['children'] = lst
                                # del item['path']
                                # del item['name']
                                # del item['component']
                                if item not in Menu:
                                    Menu.append(item)
            return Menu
        except Exception as e:
            conn.close()
            cur.close()
            print(e)

    def init(self):  # 初始化数据库
        self.cf = configparser.ConfigParser()
        self.cf.read('config.ini')
        host = self.cf.get('DBconnect', 'host')  # 远程主机
        port = self.cf.get('DBconnect', 'port')  # 端口号
        user = self.cf.get('DBconnect', 'user')  # 用户
        password = self.cf.get('DBconnect', 'password')  # 密码
        db_name = self.cf.get('DBconnect', 'db_name')  # 数据库
        charset = self.cf.get('DBconnect', 'charset')  # 字符集
        self.DBconnect_Pool = PooledDB(pymysql,
                                       5,
                                       charset=charset,
                                       host=host,
                                       user=user,
                                       passwd=password,
                                       db=db_name,
                                       port=int(port)
                                       )

    def logout(self, u_name):
        conn = self.DBconnect_Pool.connection()
        cur = conn.cursor()
        _login_status_slq = """
        UPDATE %s SET status='1' where username='%s'
         """ % (self.cf.get('DBconnect', 'sql_table'), u_name)
        try:
            cur.execute(_login_status_slq)
            return {"status": 0, "msg": '退出成功'}
        except Exception as e:
            return {"status": 1, "msg": '退出失败，请稍后重试！'}

    def LoginSql(self, u_name: str, pwd: str):  # 登录验证
        """
        :param u_name: 账号
        :param pwd: 密码
        :return: 返回数据类型为json格式，flag=0表示返回成功，1表示账号存在，但密码不正确
                2表示没该账号
        """
        # De_crypto = self.SM4.decryptData_ECB(u_name)
        EnString = self.SM4.encryptData_ECB(pwd)  # 加密密码
        # 获得数据库连接池
        conn = self.DBconnect_Pool.connection()
        cur = conn.cursor()
        try:
            SqlUserinfo = """
                    SELECT username,name,password,authority from %s where username='%s' and Isuse=0
                    """ % (self.cf.get('DBconnect', 'sql_table'), u_name)
            cur.execute(SqlUserinfo)
            conn.commit()
            result = cur.fetchall()
            authority = result[0][3]  # 权限
            resultDict = {}
            if len(result) != 0:  # 返回不为空
                if EnString == result[0][2]:
                    # 获取菜单菜单列表
                    Menu = self.getMenuList(authority)
                    resultDict['username'] = result[0][0]
                    resultDict['name'] = result[0][1]
                    resultDict['password'] = result[0][2]
                    resultDict['menu'] = Menu
                    _login_status_slq = """
                   UPDATE %s SET status='1' where username='%s'
                    """ % (self.cf.get('DBconnect', 'sql_table'), u_name)
                    cur.execute(_login_status_slq)
                    return {"status": 0, "msg": '登录成功', "result": resultDict}
                else:
                    return {"status": 1, "msg": '密码错误'}
            else:
                return {"status": 2, "msg": '抱歉，该账号不存在'}
        except Exception as e:
            conn.close()
            cur.close()
            return {"status": 3, "msg": '抱歉，发生错误'}
            print(e)

    def RegisterSql(self, pwd: str, nick: str, email: str):  # 注册验证
        """
        :param pwd: 密码
        :param nick: 昵称
        :param email: 邮箱
        :return: 返回状态0表示注册成功，1预留，2注册失败
        """
        EnString = self.SM4.encryptData_ECB(pwd)  # 加密密码
        # 获得数据库连接池
        conn = self.DBconnect_Pool.connection()
        cur = conn.cursor()
        while True:  # 随机生成账号
            c = ['6', '7', '8', '9', "10"]  # 账号位数
            a = ['1', '2', '3', '4', '5', '6', '7', '8', '9']  # 第一位位数
            b = ['1', '2', '3', '4', '5', '6', '7', '8', '9', '0']  # 其余位数
            _c = random.choice(c)
            _b = ''
            _a = random.choice(a)
            for i in range(int(_c) - 1):
                _b += random.choice(b)
            account = _a + _b
            SqlUserinfoSelect = """
            SELECT username from %s where username='%s'
            """ % (self.cf.get('DBconnect', 'sql_table'), account)
            try:
                cur.execute(SqlUserinfoSelect)
                conn.commit()
                SelectResult = cur.fetchall()
                if not SelectResult:  # 如果结果返回空，则表示该账号未注册过，否则重新生成
                    break
                else:
                    continue
            except Exception as e:
                print(e)
                return {"flag": 2, "ResultMsg": '发生未知错误，请稍后再试！'}
        SqlUserinfoInsert = """
        INSERT INTO %s(username,password,name,Email,authority,Isuse) VALUES ('%s','%s','%s','%s',1,0)
        """ % (self.cf.get('DBconnect', 'sql_table'), account, EnString, nick, email)
        try:
            cur.execute(SqlUserinfoInsert)
            conn.commit()
            return {"flag": 0, "msg": '恭喜你，注册成功', "account": account}
        except Exception as e:
            # 发生错误时回滚
            conn.rollback()
            conn.close()
            cur.close()
            print(e)
            return {"flag": 2, "msg": '哎呀，发生错误，请稍后再试吧！'}

    def UpdateSql(self):  # 修改信息
        pass

    def DelSql(self):  # 注销账号
        pass


if __name__ == '__main__':
    db = DataBase()
